package com.xpgk.mvc.statistic.budgetYearStatistic;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Calendar;
import java.util.List;

import com.jfinal.plugin.activerecord.Record;
import com.jfinal.render.Render;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class BudgetYearStatisticXLS extends  Render{
	
	private static final String CONTENT_TYPE = "application/msexcel;charset=UTF-8";

	private final List<Record> data;
	private final String searchCriteria;//搜索条件
	private final String fileName;

	public BudgetYearStatisticXLS(List<Record> data,String searchCriteria, String fileName) {
		this.data = data;
		this.searchCriteria=searchCriteria;
		this.fileName = fileName;
	}

	
	
	@Override
	public void render() {
		// TODO Auto-generated method stub
		this.response.reset();
		try {
			this.response.setHeader("Content-disposition",
					"attachment; filename*=UTF-8''" + URLEncoder.encode(this.fileName, "UTF-8") + ".xls");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		this.response.setContentType(CONTENT_TYPE);

		OutputStream os = null;
		try {
			os = this.response.getOutputStream();
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet(this.fileName, 0);
			
			Calendar calendar=Calendar.getInstance();
			int year=calendar.get(Calendar.YEAR);
			
			String[] main_header={fileName,"","","","","","",""};
			String[] search_header={"搜索条件：",searchCriteria,"","","","","",""};
			String[] headerss={"产品名称",(year-1)+"年全年进货量",(year-1)+"年月平均进货量",year+"年1-9月进货量",year+"年10月预计进货量",year+"年11月预计进货量",year+"年12月预计进货量",year+"年全年预计进货量",year+"年月平均预计进货量",(year+1)+"年1月",
					(year+1)+"年2月",(year+1)+"年3月",(year+1)+"年4月",(year+1)+"年5月",(year+1)+"年6月",(year+1)+"年7月",(year+1)+"年8月",(year+1)+"年9月",(year+1)+"年10月",(year+1)+"年11月",(year+1)+"年12月",(year+1)+"年全年预计进货量",(year+1)+"年月平均预计进货量"};
		    String[] columnss={"proname","oldyearpurchased","oldmonthpurchased","now9purchased","now10willpurchased","now11willpurchased","now12willpurchased","nowyearwillpurchased","nowmonthwillpurchased","januarynum","februarynum","marchnum",
		    		"aprilnum","maynum","junenum","julynum","augustnum","septembernum","octobernum","novembernum","decembernum","yearsum","monthnum"};
		   
		    int row = 0;
		    
		    for(int s=0;s<headerss.length-2;s++)
		    {
		    	sheet.setColumnView(s, 15); // 设置列的宽度
		    }
		    sheet.setColumnView(21, 25);
		    sheet.setColumnView(22, 25);
			sheet.setRowView(0, 600); // 设置行的高度
			sheet.setRowView(1, 600); // 设置行的高度
			sheet.setRowView(2, 600); // 设置行的高度
			
			//合并单元格
			sheet.mergeCells(0, 0, 22, 0);// 列，行，列，行
			sheet.mergeCells(1, 1, 22, 1);
			//主表头样式
			WritableFont wf = new WritableFont(WritableFont.TIMES, 18,  WritableFont.BOLD, true);
			WritableCellFormat wcfF = new WritableCellFormat(wf);
			wcfF.setBackground(Colour.GRAY_25);// 背景色
			wcfF.setAlignment(Alignment.CENTRE);// 水平对齐
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			wcfF.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//搜索表头样式
			WritableCellFormat search_format = new WritableCellFormat();
			search_format.setBackground(Colour.GRAY_25);// 背景色
			search_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			search_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//表头样式
			WritableCellFormat header_format = new WritableCellFormat();
			header_format.setBackground(Colour.GRAY_25);// 背景色
			header_format.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//生成主表头
			for (int mi = 0; mi < main_header.length; mi++) {
				String hea = main_header[mi];
				sheet.addCell(new Label(mi, row, hea, wcfF));
			}
			row++;
			//生成搜索表头
			for (int si = 0; si < search_header.length; si++) {
				String hea = search_header[si];
				if(si==0)
				{
					sheet.addCell(new Label(si, row, hea, header_format));
				}else
				{
					sheet.addCell(new Label(si, row, hea, search_format));
				}
				
			}
			row++;
			//生成表头
			for (int i = 0; i < headerss.length; i++) {
				String hea = headerss[i];
				sheet.addCell(new Label(i, row, hea, header_format));
			}
			row++;
			//表格样式
			WritableCellFormat row_format = new WritableCellFormat();
			row_format.setAlignment(Alignment.CENTRE);// 水平对齐
			row_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			row_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
		
//			//生成表
			for (int j =0; j < data.size(); j++) {
				Record record = (Record) data.get(j);
	
				String[] values = getValues(columnss, record);
				for (int i = 0; i < values.length; i++) {
					sheet.addCell(new Label(i, row, values[i],row_format));
				}
				row++;
			}
			workbook.write();
			workbook.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
//	private static String[] getValues(List<String> columns, Record record) {
//		String[] values = new String[columns.size()];
//		int i = -1;
//		for (String col : columns) {
//			i++;
//			Object value = record.get(col);
//			if (value == null) {
//				continue;
//			}
//			values[i] = value.toString();
//		}
//		return values;
//	}
	private static String[] getValues(String[] columns, Record record) {
		String[] values = new String[columns.length];
		int i = -1;
		for (String col : columns) {
			i++;
			Object value = record.get(col);
			if (value == null) {
				continue;
			}
			values[i] = value.toString();
		}
		return values;
	}
}

